<?php
/*********************************************
 *  CPG Dragonfly™ CMS
 *********************************************
	Copyright © since 2010 by CPG-Nuke Dev Team
	http://dragonflycms.org

	Dragonfly is released under the terms and conditions
	of the GNU GPL version 2 or any later version

	$Source$
	$Revision$
	$Author$
	$Date$
**********************************************/
# (binary) casting and b prefix forward support was added in PHP 5.2.1

class Poodle_SQL
{
	const
		STORE_RESULT = 0,
		UNBUFFERED   = 1,
		ADD_PREFIX   = 2,

		ASSOC = 1,
		NUM   = 2,
		BOTH  = 3;

	public
		$total_queries = 0,
		$total_time = 0,
		$querylist = array(),
		$mngr;

	protected
		$transaction = false,
		$tbl_pattern = null,
		$tbl_replace = null,

		$TBL,        // database tables
		$DBM = null, // master database
		$DBS = null, // slave database (optional)
		$XML;

	private static $path;

	function __construct($adapter, $master_config, $prefix, $slave_config=null)
	{
		if (!self::$path) { self::$path = realpath(dirname(__FILE__)).'/'; }
		if (!$adapter) { throw new Exception('Poodle SQL adapter not configured'); }
		$adapter_file = self::$path.'adapter/'.strtolower($adapter).'.php';
		if (!is_readable($adapter_file)) { throw new Exception('Poodle SQL adapter not found'); }
		include_once($adapter_file);
		$adapter = 'Poodle_SQL_Adapter_'.$adapter;
		$this->DBM = new $adapter($master_config);
		$this->DBS = $slave_config ? new $adapter($slave_config) : $this->DBM;
		$this->TBL = new Poodle_SQL_Tables($this, $prefix, constant(get_class($this->DBM).'::TBL_QUOTE'));
		$this->debug = Poodle::$DEBUG;
	}

	public function close() { return $this->DBM->close() && ($this->DBS!==$this->DBM ? $this->DBS->close() : true); }

	public function query($query, $options=0)
	{
		$qtime = microtime(true);
		$query = trim($query);
		$query[0] = strtoupper($query[0]);
		if ($this->DBS!==$this->DBM && strspn($query, 'SE', 0, 1)) {
			$result = $this->DBS->query($query, $options & self::UNBUFFERED);
		} else
		try {
			if ($options & self::ADD_PREFIX)
			{
				if (strspn($query, 'CA', 0, 1))
				{
					if (!$this->tbl_pattern) { require(self::$path.'convert/'.strtolower($this->engine).'.php'); }
					$query = preg_replace($this->tbl_pattern, $this->tbl_replace, $query);
					if (!$query) { return true; }
				}
				if ('I' === $query[0])
				{
					$query = preg_replace('#([\(,]\s*)0x(([0-9a-f]{2}?)+)(\s*)#e', '\'$1\'.$this->DBM->quoteBinary(pack(\'H*\', \'$2\')).\'$4\'', $query);
				}
				$query = preg_replace('#{([a-z0-9_]+)}#', "{$this->TBL->prefix}\$1", $query);
/*
				$query = preg_replace(
					'#^(INSERT INTO|(?:CREATE|ALTER)[\w\s]+|DROP TABLE|UPDATE|COMMENT ON (?:COLUMN|TABLE)|\) REFERENCES|FROM|JOIN)\s+{([a-z0-9_]+)}#',
					"\$1 {$this->TBL->prefix}\$2", $query);
*/
			}
			$result = $this->DBM->query($query, $options & self::UNBUFFERED);
		} catch (Poodle_SQL_Exception $e) {
			$this->rollback();
			throw $e;
		}
		$this->log($query, microtime(true)-$qtime);
		return $result;
	}

	public function exec($query, $add_prefix=false)
	{
		$this->query($query, self::UNBUFFERED | ($add_prefix ? self::ADD_PREFIX : 0));
		return $this->DBM->affected_rows;
	}

	protected function log(&$query, $qtime)
	{
		++$this->total_queries;
		$this->total_time += $qtime;
		if ($this->DBM->debug || $qtime > 1)
		{
			$tmp = debug_backtrace();
			$file = ''; $line = 0;
			for ($i=0; $i<count($tmp); ++$i) {
				if (isset($tmp[$i]['file'], $tmp[$i]['line'])) {
					$file = $tmp[$i]['file'];
					$line = $tmp[$i]['line'];
					if (!strpos($file, '\\sql\\') && !strpos($file, '/sql/')) { break; }
				}
			}
			$file = Poodle::shortFilePath($file);
			if ($qtime > 1) { Poodle_LOG::notice('Slow SQL', "Slow Query {$qtime} in {$file}#{$line}:\n\n".$query); }
			if ($this->DBM->debug & Poodle::DBG_SQL_QUERIES) { $this->querylist[$file][] = array('line'=>$line, 'query'=>$query, 'time'=>$qtime); }
		}
	}

	/***********************************
	 *     Special added functions
	 ***********************************/

	public function resultToCSV($result, $filename, $delimiter=',', $headers=true)
	{
		if (false===strpos($filename, '/')) {
			header('Content-Type: text/csv');
			header('Content-Disposition: attachment;filename='.$filename);
			$fp = fopen('php://output', 'w');
			Poodle::ob_clean();
		} else {
			$fp = fopen($filename, 'w');
		}
		if ($fp) {
			$r = $result->fetch_assoc();
			if ($headers) { fputcsv($fp, array_keys($r), $delimiter); }
			do {
				fputcsv($fp, $r, $delimiter);
			} while ($r = $result->fetch_row());
			fclose($fp);
			return true;
		}
		return false;
	}

	public function quote($str) { return '\''.$this->DBM->escape_string($str).'\''; }

	public function count($table, $where='')
	{
		$tbl = $this->TBL->getTable($table);
		return $tbl ? $tbl->count($where) : false;
	}

	public function uQuery($query) { return $this->query($query, self::UNBUFFERED); }

	public function uFetchAssoc($query, $type_cast=false)
	{
		$result = $this->query($query.' LIMIT 1', self::UNBUFFERED);
		if (is_bool($result)) { return $result; }
		$row = $result->fetch_assoc($type_cast);
		$result->free();
		return $row;
	}

	public function uFetchRow($query, $type_cast=false)
	{
		$result = $this->query($query.' LIMIT 1', self::UNBUFFERED);
		if (is_bool($result)) { return $result; }
		$row = $result->fetch_row($type_cast);
		$result->free();
		return $row;
	}

	public function uFetchAll($query, $type=self::ASSOC)
	{
		$result = $this->query($query, self::UNBUFFERED);
		if (is_bool($result)) { return $result; }
		$rows = $result->fetch_all($type);
		$result->free();
		return $rows;
	}

	public function fetchFieldNames($result)
	{
		$prefixes = func_get_args();
		array_shift($prefixes);
		$prefixes = '#^('.implode('|',$prefixes).')_#';
		$fields = array();
		$result->field_seek(0);
		$i = $result->field_count;
		while ($i>0 && $field = $result->fetch_field()) { $fields[preg_replace($prefixes, '', $field->name)] = $field->name; --$i; }
		return $fields;
	}

	public function delete($table, $where)
	{
		return $this->TBL->getTable($table)->delete($where);
	}

	public function insert($table, $array, $id='')
	{
		return $this->TBL->getTable($table)->insert($array, $id);
	}

	public function insertPrepared($table, &$array, $id='')
	{
		return $this->TBL->getTable($table)->insertPrepared($array, $id);
	}

	public function update($table, $array, $where)
	{
		return $this->TBL->getTable($table)->update($array, $where);
	}

	public function updatePrepared($table, $array, $where)
	{
		return $this->TBL->getTable($table)->updatePrepared($array, $where);
	}

	public function escapeValues(&$array)
	{
		if (!is_array($array) || !$array) { return false; }
		foreach ($array as $field => &$value) { $value = $this->fixValue($value); }
		return $array;
	}

	public function fixValue($value)
	{
//		if (SQLDEFAULT===$value) { return 'DEFAULT'; }
		if (is_null($value))   { return 'NULL'; }
		if (is_bool($value))   { return ($value?1:0); }
		if (is_int($value))    { return $value; }
		if (is_binary($value)) { return $this->DBM->quoteBinary($value); }
		if (is_float($value))  { return number_format($value, 14, '.', ''); }
		if ($value instanceof DateTime) {
			$value->setTimezone(new DateTimeZone('UTC')); // Always store dates in UTC timezone
			if ($value instanceof Poodle_Timestamp) { return $value->getTimestamp(); }
			else if ($value instanceof Poodle_Date) { $value = $value->format('Y-m-d'); }
			else if ($value instanceof Poodle_Time) { $value = $value->format('H:i:s'); }
			else                                    { $value = $value->format('Y-m-d\TH:i:s'); }
		}
		return $this->quote($value);
	}

	public function parseWhere($where)
	{
		if (is_array($where)) {
			foreach ($where as $field => &$value) { $value = $field.'='.$this->fixValue($value); }
			$where = implode(' AND ', $where);
		}
		return ' WHERE '.$where;
	}

	public function begin()
	{
		if ($this->transaction) { throw new Exception('SQL Transaction already started.'); }
		return $this->transaction = $this->DBM->begin();
	}

	public function commit()
	{
		if (!$this->transaction) { return true; }
		$this->transaction = false;
		return $this->DBM->commit();
	}

	public function rollback()
	{
		if (!$this->transaction) { return false; }
		$this->transaction = false;
		return $this->DBM->rollback();
	}

	public function removePrefix(&$array)
	{
		$newarray = array();
		$prefixes = func_get_args();
		array_shift($prefixes);
		$prefixes = '#^('.implode('|',$prefixes).')_#';
		foreach ($array as $key => $value) {
			$newarray[preg_replace($prefixes, '', $key)] = $value;
		}
		$array = $newarray;
	}

	public function search(array $fields, &$text) { return $this->DBS->search($fields, $text); }

	function __get($key)
	{
		switch ($key)
		{
		case 'database': return $this->DBM->dbname();
		case 'TBL':      return $this->TBL;
		case 'XML':
			if (!$this->XML) {
				include_once(self::$path.'xml.php');
				include_once(self::$path.'xml/'.strtolower($this->engine).'.php');
				$class = 'Poodle_SQL_XML_'.$this->engine;
				$this->XML = new $class($this);
			}
			return $this->XML;
		case 'engine':   return constant(get_class($this->DBM).'::ENGINE');
		case 'tbl_quote':return constant(get_class($this->DBM).'::TBL_QUOTE');
		case 'debug':
		case 'affected_rows':
		case 'client_info':
		case 'client_version':
//		case 'connect_errno';
//		case 'connect_error';
		case 'errno':
		case 'error':
//		case 'field_count':
		case 'host_info':
//		case 'protocol_version':
		case 'server_info':
		case 'server_version':
//		case 'info':
		case 'insert_id':
		case 'sqlstate':
//		case 'thread_id':
//		case 'warning_count':
			return $this->DBM->$key;
		}
		return null;
	}

	function __set($key, $value)
	{
		if ('debug' === $key)
		{
			$this->DBM->debug = $value;
			$this->DBS->debug = $value;
		}
	}

	function __call($method, $args)
	{
		switch ($method)
		{
		# bugs.php.net/bug.php?id=49535
//		case 'get_client_info':
//		case 'get_client_version':
//			return $this->DBM->substr($method,4);

		case 'character_set_name':
		case 'dbname':
		case 'get_charset':
		case 'ping':
		case 'stat':
			return $this->DBM->$method();

		case 'autocommit':
		case 'change_user':
		case 'debug':
		case 'dump_debug_info':
		case 'get_cache_stats':      # 5.3.0 mysqlnd
		case 'get_client_stats':     # 5.3.0 mysqlnd
		case 'get_connection_stats': # 5.3.0 mysqlnd
		case 'get_warnings':
		case 'kill':
		case 'more_results':
		case 'multi_query':
		case 'next_result':
		case 'options':
		case 'poll':
		case 'prepare':
		case 'reap_async_query':
		# internal
		case 'init':
		case 'ssl_set':
		case 'store_result':
		case 'use_result':
			trigger_error('Call to unsupported method Poodle_SQL::'.$method.'()', E_USER_ERROR);

		case 'quoteBinary':
		case 'escapeBinary':
		case 'unescapeBinary':
		case 'escape_string':
		case 'insert_id':
			return $this->DBM->$method($args[0]);
		case 'showTables':
			return $this->DBS->$method($args[0]);
		}
		if (empty($this->mngr)) {
			include_once(self::$path.'manager/'.strtolower($this->engine).'.php');
			$mngr = 'Poodle_SQL_Manager_'.$this->engine;
			$this->mngr = new $mngr($this);
		}
		if ('load_manager' === $method) { return; }
		return call_user_func_array(array($this->mngr, $method), $args); # this is slow
	}

}

class Poodle_SQL_Table
{
	protected
		$name,
		$db;

	function __construct($name, Poodle_SQL $db)
	{
		$this->name = $name;
		$this->db   = $db;
	}

	public function __toString() { return $this->name; }

	public function insert(array $array, $id='')
	{
		$this->db->escapeValues($array);
		return $this->insertPrepared($array, $id);
	}

	public function insertPrepared(array $array, $id='')
	{
		if (!$array) { return false; }
		$this->db->query('INSERT INTO '.$this->name.' ('.implode(', ', array_keys($array)).') VALUES ('.implode(', ', $array).')');
		return ($id ? $this->db->insert_id($id) : true);
	}

	public function update(array $array, $where)
	{
		$this->db->escapeValues($array);
		return $this->updatePrepared($array, $where);
	}

	public function updatePrepared(array $array, $where)
	{
		if (!$array || !$where) { return false; }
		foreach ($array as $field => &$value) { $value = $field.'='.$value; }
		return $this->db->query('UPDATE '.$this->name
			.' SET '.implode(', ', $array)
			.$this->db->parseWhere($where)
		);
	}

	public function count($where='')
	{
		if ($where) $where = $this->db->parseWhere($where);
		$result = $this->db->uQuery('SELECT COUNT(*) FROM '.$this->name . $where);
		if (!$result) { return 0; }
		list($count) = $result->fetch_row();
		$result->free();
		return $count;
	}

	public function delete($where)
	{
		return $this->db->exec('DELETE FROM '.$this->name . $this->db->parseWhere($where));
	}

	public function uFetchAssoc(array $fields, array $where)
	{
		return $this->db->uFetchAssoc(
		"SELECT ".implode(', ',$fields)." FROM {$this->name}"
		.$this->db->parseWhere($where));
	}

	public function uFetchRow(array $fields, array $where)
	{
		return $this->db->uFetchRow(
		"SELECT ".implode(', ',$fields)." FROM {$this->name}"
		.$this->db->parseWhere($where));
	}

	public function uFetchAll(array $fields, array $where, $type=self::ASSOC)
	{
		return $this->db->uFetchAll(
		"SELECT ".implode(', ',$fields)." FROM {$this->name}"
		.$this->db->parseWhere($where));
	}
}

class Poodle_SQL_Tables implements ArrayAccess, Countable
{
	protected
		$db,
		$prefix,
		$tables = array();

	function __construct(Poodle_SQL $sql, $prefix, $tbl_quote)
	{
		$this->db = $sql;
		$this->prefix = $prefix;
		$K = Poodle::getKernel();
		try {
			if ($K && $K->CACHE) { $this->tables = $K->CACHE->get(__CLASS__.'_'.$sql->database); }
		} catch (Exception $e) {}
		if (!$this->tables || POODLE_SETUP)
		{
			$result = $sql->showTables($prefix);
			$pl = strlen($this->prefix);
			$this->tables = array();
			global $user_prefix;            // dragonfly custom users table prefix
			$dful = strlen($user_prefix)+1; // dragonfly custom users table prefix
			while ($name = $result->fetch_row())
			{
				// dragonfly custom users table prefix
//				if ($name[0] === $user_prefix.'_users' || $name[0] === $user_prefix.'_users_temp')
				if (substr($name[0],0,strlen($dful)) === $user_prefix)
				{
					$k = substr($name[0],$dful);
					if (!preg_match('#^[a-zA-Z0-9_]+$#D', $name[0])) { $name[0] = $tbl_quote.$name[0].$tbl_quote; }
					$this->tables[$k] = $name[0];
				} else
				// default table prefix
				if (!$pl || substr($name[0],0,$pl) === $prefix)
				{
					$k = substr($name[0],$pl);
					if (!preg_match('#^[a-zA-Z0-9_]+$#D', $name[0])) { $name[0] = $tbl_quote.$name[0].$tbl_quote; }
					$this->tables[$k] = $name[0];
				}
			}
			$result->free();
			try {
				if ($K && $K->CACHE) { $K->CACHE->set(__CLASS__.'_'.$sql->database, $this->tables); }
			} catch (Exception $e) {}
		}
	}

	function __get($key)
	{
		if ('prefix' === $key) { return $this->prefix; }
		if (!isset($this->tables[$key])) {
			if (class_exists('Poodle_Debugger', false)) {
				Poodle_Debugger::trigger('Unknown database table: '.$key, dirname(__FILE__));
			} else {
				trigger_error('Unknown database table: '.$key);
			}
			$this->tables[$key] = $this->prefix.$key;
		}
		return $this->getTable($key);
	}

	function __set($key, $v) { throw new Exception('Disallowed to set property: '.$key); }

	function __isset($key)   { return isset($this->tables[$key]); }

	public function getTable($name)
	{
		$tbl = isset($this->tables[$name]) ? $this->tables[$name] : $this->prefix.$name;
		if (!is_object($tbl)) {
			$tbl = $this->tables[$name] = new Poodle_SQL_Table($tbl, $this->db);
		}
		return $tbl;
	}

	public function prefix() { return $this->prefix; }

	# ArrayAccess
	public function offsetExists($k)  { return array_key_exists($k, $this->tables); }
	public function offsetGet($k)     { return $this->getTable($k); }
	public function offsetSet($k, $v) { }
	public function offsetUnset($k)   { }

	# Countable
	public function count() { return count($this->tables); }
}
